﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using SMALLERP.DataClass;
using System.Data.SqlClient;

namespace XJLSC.PO
{
    public partial class CGQCZZ : Form
    {
        private readonly DataBase db = new DataBase();
        private SqlDataReader sdr1;
        public CGQCZZ()
        {
            InitializeComponent();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string strSql = "";

            strSql = "SELECT PURMA.MA002 供应商简称,TC003 AS 采购日期,PURTD.TD001 AS 采购单别,PURTD.TD002 AS 采购单号,PURTD.TD003 AS 采购序号,RTRIM(INVMB.UDF01) 老编码,TD004 品号,TD005 品名,TD006 规格,TD009 单位,CEILING(SUM(QGSL)) 请购数量,CEILING(SUM(CGSL)) 采购数量,CEILING(SUM(JHSL)) 进货数量  FROM V_CGQC Left JOIN PURTD On V_CGQC.TD001=PURTD.TD001 AND V_CGQC.TD002=PURTD.TD002 AND V_CGQC.TD003=PURTD.TD003 LEFT JOIN PURTC ON V_CGQC.TD001=PURTC.TC001 AND V_CGQC.TD002=PURTC.TC002 LEFT JOIN INVMB ON PURTD.TD004=INVMB.MB001 LEFT JOIN PURMA ON MA001=PURTC.TC004 WHERE PURTC.TC003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker1.Value.ToShortDateString() + "'),112) AND PURTC.TC003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker2.Value.ToShortDateString() + "'),112) AND (PURTD.TD005 LIKE '" + textBox3.Text + "%' OR '" + textBox3.Text + "'='') AND (PURTC.TC003 >= '" + textBox4.Text + "' OR '" + textBox4.Text + "'='') AND (PURTC.TC003 <= '" + textBox5.Text + "' OR '" + textBox5.Text + "'='') AND (INVMB.UDF01 LIKE '%" + textBox2.Text + "%' OR '" + textBox2.Text + "'='') AND (PURTC.TC004 ='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') GROUP BY TC003,PURTD.TD001,PURTD.TD002,PURTD.TD003,TD004,RTRIM(INVMB.UDF01),TD005,TD006,TD009,PURMA.MA002";

            //textBox6.Text = strSql;
            //MessageBox.Show(strSql,"提示");
            try
            {
                DataTable dt;
                dataGridView1.DataSource = "";
                dataGridView1.Refresh();

                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                //DataRow row = dt.NewRow();

                // row["老编码"] = "合计";

                //dt.Rows.Add(row.ItemArray);
                //dt.AcceptChanges();


                dataGridView1.DataSource = dt;


                this.dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                this.dataGridView1.AllowUserToAddRows = false;
                //this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 1].ReadOnly = true;
                //this.dataGridView1.Rows[this.dataGridView1.Rows.Count - 1].DefaultCellStyle.BackColor = Color.Yellow;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                //throw ex;
            }

            double sum = 0;
            for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            {
                sum += double.Parse(this.dataGridView1.Rows[i].Cells["请购数量"].Value.ToString());
            }
            textBox12.Text = sum.ToString();

            double sum1 = 0;
            for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            {
                sum1 += double.Parse(this.dataGridView1.Rows[i].Cells["采购数量"].Value.ToString());
            }
            textBox13.Text = sum1.ToString();

            double sum2 = 0;
            for (int i = 0; i <= this.dataGridView1.Rows.Count - 1; i++)
            {
                sum2 += double.Parse(this.dataGridView1.Rows[i].Cells["进货数量"].Value.ToString());
            }
            textBox14.Text = sum2.ToString();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;
            dlg.Title = "保存为Excel文件";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = null;
                try
                {
                    //写入列标题    
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        if (dataGridView1.Columns[i].Visible == true)
                        {
                            if (i > 0)
                            {
                                columnTitle += "\t";
                            }
                            columnTitle += dataGridView1.Columns[i].HeaderText;

                        }

                    }
                    sw.WriteLine(columnTitle);
                    //写入列内容    
                    for (int j = 0; j < dataGridView1.Rows.Count; j++)
                    {

                        string columnValue = null;
                        for (int k = 0; k < dataGridView1.Columns.Count; k++)
                        {
                            if (dataGridView1.Columns[k].Visible == true)
                            {
                                if (k > 0)
                                {
                                    columnValue += "\t";
                                }
                                if (dataGridView1.Rows[j].Cells[k].Value == null)
                                    columnValue += "";
                                else
                                    columnValue += dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
                            }
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }

            }
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            decimal t = 0;

            for (int i = 0; i < dataGridView1.SelectedCells.Count; i++)
            {
                if (dataGridView1.SelectedCells[i].Value.GetType() != typeof(String))
                {
                    t = t + Convert.ToDecimal(dataGridView1.SelectedCells[i].Value);
                }
            }
            textBox6.Text = t.ToString();
        }

        private void CGQCZZ_Load(object sender, EventArgs e)
        {

        }
    }
}
